﻿using _测试循环导入使用参数化.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _测试循环导入使用参数化
{
    class Program
    {
        static void Main(string[] args)
        {
            //string str = "duanwk";
            //Console.WriteLine(str.FirstToUpper());



            List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();

            Dictionary<string, object> row1 = new Dictionary<string, object>();
            row1.Add("Name", "奥特曼");
            row1.Add("RenQi", 456);
            row1.Add("InputTime", DateTime.Now.AddDays(-5));

            Dictionary<string, object> row2 = new Dictionary<string, object>();
            row2.Add("Name", "积木");
            row2.Add("RenQi", 573);
            row2.Add("InputTime", DateTime.Now.AddDays(-3));

            Dictionary<string, object> row3 = new Dictionary<string, object>();
            row3.Add("Name", "未知");
            row3.Add("RenQi", 874);
            row3.Add("InputTime", DateTime.Now.AddDays(-2));

            rows.Add(row1);
            rows.Add(row2);
            rows.Add(row3);


            SqlParameter[] para = new SqlParameter[] {
                new SqlParameter("@Name",System.Data.SqlDbType.NVarChar,128),
                new SqlParameter("@RenQi",System.Data.SqlDbType.Int),
                new SqlParameter("@InputTime",System.Data.SqlDbType.DateTime)
            };

            string sql = "insert into Product(Name,RenQi,InputTime)values(@Name,@RenQi,@InputTime)";

            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["Default"].ToString(); ;

            #region 方法一
            //using (SqlConnection conn = new SqlConnection(connStr))
            //{
            //    conn.Open();
            //    SqlTransaction tran = conn.BeginTransaction();
            //    using (SqlCommand cmd = new SqlCommand())
            //    {
            //        try
            //        {
            //            cmd.Transaction = tran;
            //            cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 128);
            //            cmd.Parameters.Add("@RenQi", SqlDbType.Int);
            //            cmd.Parameters.Add("@InputTime", SqlDbType.DateTime);
            //            cmd.Connection = conn;
            //            cmd.CommandText = sql;
            //            cmd.CommandType = System.Data.CommandType.Text;

            //            for (int i = 0; i < rows.Count; i++)
            //            {
            //                //cmd.CommandText = string.Format(sql, rows[i]["Name"], rows[i]["RenQi"], rows[i]["InputTime"]);
            //                cmd.Parameters["@Name"].Value = rows[i]["Name"];
            //                cmd.Parameters["@RenQi"].Value = rows[i]["RenQi"];
            //                cmd.Parameters["@InputTime"].Value = rows[i]["InputTime"];
            //                cmd.ExecuteNonQuery();
            //            }
            //            tran.Commit();
            //        }
            //        catch (Exception ex)
            //        {
            //            tran.Rollback();
            //        }
            //    }
            //} 
            #endregion

            #region 方法二

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        cmd.CommandText = sql;
                        cmd.CommandTimeout = 1800;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = conn;
                        cmd.Transaction = tran;
                        for (int i = 0; i < rows.Count; i++)
                        {
                            para[0].Value = rows[i]["Name"];
                            para[1].Value = rows[i]["RenQi"];
                            para[2].Value = rows[i]["InputTime"];
                            cmd.Parameters.AddRange(para);
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear(); //如果使用 SQLParameter[] para=new SqlParameter[]{}; 这是关键语句
                        }
                        tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw ex;
                    }


                }
            }

            #endregion

        }
    }
}
